﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace SystemDirectory.DAO
{
    public class AddressDAO
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());

        public DataTable Get(string PostCode, string Address, string Town)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com;
            com = new SqlCommand("SELECT A.AddressID, A.PostCode, A.AddressLine1, A.TownID, T.TownName, A.CountyID, C.CountyName, A.CountryID, CTR.CountryName"
                                +" FROM Address A JOIN Town T ON A.TownID = T.TownID"
                                               +" JOIN County C ON A.CountyID = C.CountyID"
                                               +" JOIN Country CTR ON A.CountryID = CTR.CountryID"
                                +" WHERE A.PostCode LIKE @PostCode AND A.AddressLine1 LIKE @Address AND T.TownName LIKE @Town", con);

            com.Parameters.Add("@PostCode", SqlDbType.NVarChar, 255);
            com.Parameters["@PostCode"].Value = "%" + PostCode + "%";
            com.Parameters.Add("@Address", SqlDbType.NVarChar, 255);
            com.Parameters["@Address"].Value = "%" + Address + "%";
            com.Parameters.Add("@Town", SqlDbType.NVarChar, 255);
            com.Parameters["@Town"].Value = "%" + Town + "%";
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);
            return dtResult;
        }
    }
}